import mysql.connector
from mysql.connector import Error


# db_host='127.0.0.1'
# db_user='root'
# db_password='1234'
# db_database='yilong'

db_host='118.122.44.7'
db_user='root'
db_password='qweR@123456'
db_database='yilong'
db_port=6777

def updatePrice(source, sourceType, sourceUrl, area, priceDate, priceCategory, priceVariety, price, priceUnit): 
    connection = None
    # 连接数据库
    try:
        connection = mysql.connector.connect(
            host=db_host,
            user=db_user,
            password=db_password,
            database=db_database,
            port=db_port
        )
        if connection.is_connected():
            # 创建cursor对象
            cursor = connection.cursor()

            # 判断数据是否存在
            querySql = f"SELECT COUNT(id) FROM t_market_price WHERE source='{source}' AND area='{area}' AND price_date='{priceDate}' AND price_category='{priceCategory}' AND price_variety='{priceVariety}';"
            cursor.execute(querySql) 
            count = cursor.fetchone()[0]
    
            # 执行写操作
            if count<=0:
                updateSql = f"insert into t_market_price(source,source_type,source_url,area,price_date,price_category,price_variety,price,price_unit,create_by,create_time) values('{source}','{sourceType}','{sourceUrl}','{area}','{priceDate}','{priceCategory}', '{priceVariety}', {price}, '{priceUnit}', 'system-auto', now());"
                updateValues = (source, sourceUrl,area,priceDate, priceCategory, price, priceUnit)
                cursor.execute(updateSql)
                connection.commit()
    
            # 关闭cursor
            cursor.close()
    
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection is not None and connection.is_connected():
            connection.close()


def savePriceBatch(price_info_list): 
    connection = None
    # 连接数据库
    try:
        connection = mysql.connector.connect(
            host=db_host,
            user=db_user,
            password=db_password,
            database=db_database,
            port=db_port
        )
        if connection.is_connected():
            # 创建cursor对象
            cursor = connection.cursor()
            sql = "insert into t_market_price(source,source_type,source_url,area,price_date,price_category,price_variety,price,price_unit,create_by,create_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s, 'system-auto', now())"
            val = []
            for price_info in price_info_list:
                # 判断数据是否存在
                querySql = f"SELECT COUNT(id) FROM t_market_price WHERE source='{price_info[0]}' AND area='{price_info[3]}' AND price_date='{price_info[4]}' AND price_category='{price_info[5]}' AND price_variety='{price_info[6]}';"
                cursor.execute(querySql)
                count = cursor.fetchone()[0]
        
                # 执行写操作
                if count<=0:
                    sqlValues = (price_info[0],price_info[1],price_info[2],price_info[3],price_info[4],price_info[5],price_info[6],price_info[7],price_info[8])
                    val.append(sqlValues)
            
            cursor.executemany(sql, val)
            connection.commit()

            # 关闭cursor
            cursor.close()
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection is not None and connection.is_connected():
            connection.close()


def updatePopularFeelings(source, url, sourceType, sourceDate, isPopular, keywords, title):
    # 连接数据库
    connection = None
    try:
        connection = mysql.connector.connect(
            host=db_host,
            port=db_port,
            user=db_user,
            password=db_password,
            database=db_database
        )
        if connection.is_connected():
            # 创建cursor对象
            cursor = connection.cursor()

            # 判断数据是否存在
            querySql = f"SELECT COUNT(id) FROM t_popular_feelings WHERE popular_url='{url}' AND popular_title='{title}';"
            cursor.execute(querySql)
            count = cursor.fetchone()[0]

            # 执行写操作
            if count<=0:
                updateSql = f"insert into t_popular_feelings(popular_source,popular_url,popular_time,popular_type,is_popular,popular_keywords,popular_title,popular_flag,remark,create_by,create_time) values('{source}','{url}','{sourceDate}','{sourceType}','{isPopular}','{keywords}', '{title}', '', '', 'system-auto', now());"

                cursor.execute(updateSql)
                connection.commit()

            # 关闭cursor
            cursor.close()

    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection is not None and connection.is_connected():
            connection.close()